go 
use Mobile
go

CREATE PROC sp_Select_AllServicesType
AS
	BEGIN
		select * from tblServiceType
	END

go
go

CREATE PROC sp_Select_OtherServicesType
AS
	BEGIN
		select * from tblServiceType where id_ST!=1 and id_ST!=2
	END

go
CREATE PROC sp_Select_Service
@serviceName nvarchar(30)
AS
	BEGIN
		select * from tblServiceDetail inner join tblServiceType on tblServiceDetail.id_ST=tblServiceType.id_ST
			where tblServiceType._serviceName=@serviceName
	END
	
go
exec sp_Select_Service @serviceName='TopUp'
go
CREATE PROC sp_Select_ServiceActive
@serviceName nvarchar(30)
AS
	BEGIN
		select * from tblServiceDetail inner join tblServiceType on tblServiceDetail.id_ST=tblServiceType.id_ST
			where tblServiceType._serviceName=@serviceName and tblServiceDetail._status=1
	END

go
CREATE PROC sp_Select_OtherService
AS
	BEGIN
		select * from tblServiceDetail inner join tblServiceType on tblServiceDetail.id_ST=tblServiceType.id_ST
			where tblServiceType.id_ST!=1 and tblServiceType.id_ST!=2
	END
go

CREATE PROC sp_Select_OtherServiceActive
AS
	BEGIN
		select * from tblServiceDetail inner join tblServiceType on tblServiceDetail.id_ST=tblServiceType.id_ST
			where tblServiceType.id_ST!=1 and tblServiceType.id_ST!=2 and tblServiceDetail._status=1;
	END
go
CREATE PROC sp_Select_ServiceDetail
@serviceName nvarchar(30),
@id_SD int
AS
	BEGIN
		select * from tblServiceDetail inner join tblServiceType on tblServiceDetail.id_ST=tblServiceType.id_ST
			where tblServiceType._serviceName=@serviceName and tblServiceDetail.id_SD=@id_SD
	END


go
CREATE PROC sp_Select_AllBills
AS
	BEGIN
		select * from tblBill
	END
go

CREATE PROC sp_Select_BillsByService
@serviceName nvarchar(30)
AS
	BEGIN
		select * from tblBill inner join tblServiceDetail on tblBill.id_SD=tblServiceDetail.id_SD 
			inner join tblServiceType on tblServiceDetail.id_ST=tblServiceType.id_ST
			where tblServiceType._serviceName=@serviceName
	END

go
exec sp_Select_BillsByService @serviceName='TopUp'
go
CREATE PROC sp_Select_AllBillsByMobile
@_phone nvarchar(20)
AS 
	BEGIN
		select * from tblBill inner join tblMobileNumber on tblBill.id_MN=tblMobileNumber.id_MN
			where tblMobileNumber._phone=@_phone
	END

go
exec sp_Select_AllBillsByMobile '0123'
go
CREATE PROC sp_Select_BillsByServiceAndMobile
@_phone nvarchar(20),
@_serviceName nvarchar(30)
AS
	BEGIN
		Select * from tblBill inner join tblMobileNumber on tblBill.id_MN=tblMobileNumber.id_MN
			inner join tblServiceDetail on tblBill.id_SD=tblServiceDetail.id_SD
			inner join tblServiceType on tblServiceDetail.id_ST=tblServiceType.id_ST
			where tblMobileNumber._phone=@_phone and tblServiceType._serviceName=@_serviceName
	END
go
exec sp_Select_BillsByServiceAndMobile '0123','TopUp'
go
CREATE PROC sp_Insert_Bill
@id_Bill int output,
@id_MN int,
@id_SD int,
@_createDate datetime,
@_billStatus bit
AS
	BEGIN		
		if(@_billStatus=1)		
			insert into tblBill(id_MN,id_SD,_createDate,_paymentDate,_billStatus) 
				values(@id_MN,@id_SD,@_createDate,@_createDate,1)		
		else
			insert into tblBill(id_MN,id_SD,_createDate,_billStatus) 
				values(@id_MN,@id_SD,@_createDate,0)		
	END
go

CREATE PROC sp_Select_MobileByNumber
@_phone nvarchar(20)
AS
	BEGIN
		select * from tblMobileNumber where _phone=@_phone
	END
go
exec sp_Select_MobileByNumber @_phone='0123'
go
CREATE PROC sp_Select_BillAdv
@id_MN int,
@id_ST int,
@_billStatus bit,
@_order nvarchar(20),
@_desc nvarchar(10),
@_createDate nvarchar(20),
@_paymentDate nvarchar(20)
AS
	BEGIN
	DECLARE @SQLStament nvarchar(555)
	IF @_createDate='' and @_paymentDate=''
		Set @SQLStament='select * from tblBill inner join tblServiceDetail on tblBill.id_SD=tblServiceDetail.id_SD
			inner join tblMobileNumber on tblMobileNumber.id_MN=tblBill.id_MN
			where tblServiceDetail.id_ST= '+convert(nvarchar(10),@id_ST)+ ' and tblMobileNumber.id_MN='+
			CONVERT(nvarchar(10),@id_MN)+' and tblBill._billStatus= '+convert(nvarchar(10),@_billStatus)+
			' order by '+ CONVERT(nvarchar(30),@_order)+ ' '+ CONVERT(nvarchar(30),@_desc)
	IF @_createDate!='' and @_paymentDate=''
		Set @SQLStament='select * from tblBill inner join tblServiceDetail on tblBill.id_SD=tblServiceDetail.id_SD
			inner join tblMobileNumber on tblMobileNumber.id_MN=tblBill.id_MN
			where tblServiceDetail.id_ST= '+convert(nvarchar(10),@id_ST)+ ' and tblMobileNumber.id_MN='+
			CONVERT(nvarchar(10),@id_MN)+' and tblBill._billStatus= '+convert(nvarchar(10),@_billStatus)+
			'and (DATEPART(YEAR,tblBill._createDate)= '+CONVERT(nvarchar(30),@_createDate)+
			'or DATEPART(Day,tblBill._createDate)= '+CONVERT(nvarchar(30),@_createDate)+
			'or DATEPART(M,tblBill._createDate)= '+CONVERT(nvarchar(30),@_createDate)+
			') order by '+ CONVERT(nvarchar(30),@_order)+ ' '+ CONVERT(nvarchar(30),@_desc)
	IF @_createDate='' and @_paymentDate!=''
		Set @SQLStament='select * from tblBill inner join tblServiceDetail on tblBill.id_SD=tblServiceDetail.id_SD
			inner join tblMobileNumber on tblMobileNumber.id_MN=tblBill.id_MN
			where tblServiceDetail.id_ST= '+convert(nvarchar(10),@id_ST)+ ' and tblMobileNumber.id_MN='+
			CONVERT(nvarchar(10),@id_MN)+' and tblBill._billStatus= '+convert(nvarchar(10),@_billStatus)+
			'and (DATEPART(YEAR,tblBill._paymentDate)= '+CONVERT(nvarchar(30),@_paymentDate)+
			'or DATEPART(Day,tblBill._paymentDate)= '+CONVERT(nvarchar(30),@_paymentDate)+
			'or DATEPART(M,tblBill._paymentDate)= '+CONVERT(nvarchar(30),@_paymentDate)+
			') order by '+ CONVERT(nvarchar(30),@_order)+ ' '+ CONVERT(nvarchar(30),@_desc)
	exec(@SQLStament)				
	END
go
exec sp_Select_BillAdv 1,2,0,'_createDate','asc','2012',''
go
CREATE PROC sp_Select_BillStatisticByDay
@id_MN int,
@id_ST int,
@_billStatus bit,
@_order nvarchar(20),
@_desc nvarchar(10),
@_day nvarchar(20)
AS
	BEGIN
	DECLARE @SQLStament nvarchar(555)	
		Set @SQLStament='select * from tblBill inner join tblServiceDetail on tblBill.id_SD=tblServiceDetail.id_SD
			inner join tblMobileNumber on tblMobileNumber.id_MN=tblBill.id_MN
			where tblServiceDetail.id_ST= '+convert(nvarchar(10),@id_ST)+ ' and tblMobileNumber.id_MN='+
			CONVERT(nvarchar(10),@id_MN)+' and tblBill._billStatus= '+convert(nvarchar(10),@_billStatus)+
			'and (DATEPART(YEAR,tblBill._paymentDate)= '+CONVERT(nvarchar(30),@_paymentDate)+
			'or DATEPART(Day,tblBill._paymentDate)= '+CONVERT(nvarchar(30),@_paymentDate)+
			'or DATEPART(M,tblBill._paymentDate)= '+CONVERT(nvarchar(30),@_paymentDate)+
			') order by '+ CONVERT(nvarchar(30),@_order)+ ' '+ CONVERT(nvarchar(30),@_desc)
	exec(@SQLStament)				
	END

go
select * from tblBill where DATEPART(DAY,tblBill._createDate)=13 or DATEPART(YYYY,tblBill._createDate)=2012
go
CREATE PROC sp_Select_BillOfOtherService
@id_MN int,
@_billStatus bit,
@_order nvarchar(20),
@_desc nvarchar(10),
@_createDate nvarchar(20),
@_paymentDate nvarchar(20)
AS
	BEGIN
	DECLARE @SQLStament nvarchar(555)
	IF @_createDate='' and @_paymentDate=''
		Set @SQLStament='select * from tblBill inner join tblServiceDetail on tblBill.id_SD=tblServiceDetail.id_SD
			inner join tblMobileNumber on tblMobileNumber.id_MN=tblBill.id_MN
			where tblServiceDetail.id_ST!=1 and tblServiceDetail.id_ST!=2 and tblMobileNumber.id_MN='+
			CONVERT(nvarchar(10),@id_MN)+' and tblBill._billStatus= '+convert(nvarchar(10),@_billStatus)+
			' order by '+ CONVERT(nvarchar(30),@_order)+ ' '+ CONVERT(nvarchar(30),@_desc)
	IF @_createDate='' and @_paymentDate!=''
		Set @SQLStament='select * from tblBill inner join tblServiceDetail on tblBill.id_SD=tblServiceDetail.id_SD
			inner join tblMobileNumber on tblMobileNumber.id_MN=tblBill.id_MN
			where tblServiceDetail.id_ST!=1 and tblServiceDetail.id_ST!=2 and tblMobileNumber.id_MN='+
			CONVERT(nvarchar(10),@id_MN)+' and tblBill._billStatus= '+convert(nvarchar(10),@_billStatus)+
			'and (DATEPART(YEAR,tblBill._paymentDate)= '+CONVERT(nvarchar(30),@_paymentDate)+
			'or DATEPART(Day,tblBill._paymentDate)= '+CONVERT(nvarchar(30),@_paymentDate)+
			'or DATEPART(M,tblBill._paymentDate)= '+CONVERT(nvarchar(30),@_paymentDate)+
			') order by '+ CONVERT(nvarchar(30),@_order)+ ' '+ CONVERT(nvarchar(30),@_desc)
	IF @_createDate!='' and @_paymentDate=''
		Set @SQLStament='select * from tblBill inner join tblServiceDetail on tblBill.id_SD=tblServiceDetail.id_SD
			inner join tblMobileNumber on tblMobileNumber.id_MN=tblBill.id_MN
			where tblServiceDetail.id_ST!=1 and tblServiceDetail.id_ST!=2 and tblMobileNumber.id_MN='+
			CONVERT(nvarchar(10),@id_MN)+' and tblBill._billStatus= '+convert(nvarchar(10),@_billStatus)+
			'and (DATEPART(YEAR,tblBill._createDate)= '+CONVERT(nvarchar(30),@_createDate)+
			'or DATEPART(Day,tblBill._createDate)= '+CONVERT(nvarchar(30),@_createDate)+
			'or DATEPART(M,tblBill._createDate)= '+CONVERT(nvarchar(30),@_createDate)+
			') order by '+ CONVERT(nvarchar(30),@_order)+ ' '+ CONVERT(nvarchar(30),@_desc)
	exec(@SQLStament)				
	END
go
exec sp_Select_BillOfOtherService 1,0,'_createDate','asc','21',''
go

CREATE PROC sp_Update_PaidBill
@id_Bill int,
@_paymentDate datetime
AS
	BEGIN
		update tblBill Set _billStatus=1, _paymentDate=@_paymentDate where id_Bill=@id_Bill
	END
go
use Mobile
CREATE PROC sp_Select_UserAccountBy
@_searchColumn nvarchar(20),
@_searchString nvarchar(20)
AS
	BEGIN
	DECLARE @SQLStatement nvarchar(555)
	IF @_searchString!=''		
		Set @SQLStatement='select * from tblUserAccount inner join tblUserDetail on tblUserAccount.id_UA=tblUserDetail.id_UA
			where '+CONVERT(nvarchar(30),@_searchColumn)+' like '+CHAR(39)+'%'+CONVERT(nvarchar(30),@_searchString)+'%'+CHAR(39)					
	ELSE		
		Set @SQLStatement='select * from tblUserAccount inner join tblUserDetail on tblUserAccount.id_UA=tblUserDetail.id_UA'
	exec(@SQLStatement)
	END
go
exec sp_Select_UserAccountBy '',''
go
CREATE PROC sp_Select_MobileByID_UA
@id_UA int
AS
	BEGIN
		select * from tblMobileNumber inner join tblUserAccount on tblMobileNumber.id_UA=tblUserAccount.id_UA
		   where tblUserAccount.id_UA=@id_UA
	END
go
exec sp_Select_MobileByID_UA 1

go
CREATE PROC sp_Insert_ServiceDetail
@id_ST int,
@_packageName nvarchar(20),
@_durationDate int,
@_durationTime int,
@_description nvarchar(300),
@_denomination float,
@_discount int,
@_total float,
@_status bit
AS
	BEGIN
		insert into tblServiceDetail(id_ST,_packageName,_durationDate,_durationTime,_description,_denomination,_discount,_total,_status)
			values(@id_ST,@_packageName,@_durationDate,@_durationTime,@_description,@_denomination,@_discount,@_total,@_status)
	END
go



go
CREATE PROC sp_Select_CountBillById_SD
@id_SD int
AS
	BEGIN
		select COUNT(id_Bill) from tblBill where id_SD=@id_SD
	END
go
exec sp_Select_CountBillById_SD 1
go

CREATE PROC sp_Update_ServiceDetail
@id_SD int,
@_packageName nvarchar(30),
@_durationDate int,
@_durationTime int,
@_description nvarchar(100),	
@_denomination float,
@_discount int,
@_total float
AS
	BEGIN
		update tblServiceDetail set _packageName=@_packageName,_durationDate=@_durationDate,_description=@_description,
		_denomination=@_denomination, _durationTime=@_durationTime,_discount=@_discount,_total=@_total where id_SD=@id_SD
	END
go
CREATE PROC sp_Update_ServiceType
@id_ST int,
@_serviceName nvarchar(20)
AS 
	BEGIN
		update tblServiceType set _serviceName=@_serviceName where id_ST=@id_ST
	END
go
CREATE PROC sp_Update_ServiceDetailStatus
@id_SD int,
@_status bit
AS 
	BEGIN
		update tblServiceDetail set _status=@_status where id_SD=@id_SD
	END
go
CREATE PROC sp_Update_ServiceTypeStatus
@id_ST int,
@_status bit
AS 
	BEGIN
		update tblServiceType set _status=@_status where id_ST=@id_ST
		update tblServiceDetail set _status=@_status where id_ST=@id_ST
	END
go
CREATE PROC sp_Insert_ServiceType
@_serviceName nvarchar(20),
@_status bit
AS
	BEGIN
		insert into tblServiceType(_serviceName,_status) values(@_serviceName,@_status)
	END
go
CREATE PROC sp_Update_UserAccount
@id_UA int,
@_fullName nvarchar(30),
@_birthday nvarchar(30),
@_sex bit,
@_email nvarchar(50),
@_adress nvarchar(50)
AS
	BEGIN
		update tblUserDetail set _adress=@_adress,_dateOfBirth=@_birthday,_email=@_email,_name=@_fullName,_sex=@_sex
				where id_UA=@id_UA
	END 
exec sp_Update_UserAccount 2,'Thang','01/27/2013',1,'aaaaa','HN'
go

CREATE PROC sp_Delete_MobileNumber
@id_MN int 
AS
	BEGIN
		
		delete from tblBill where id_MN=@id_MN;
		delete from tblServiceType_MobileNumber where id_MN=@id_MN;
		delete from tblMobileNumber where id_MN=@id_MN;
	END
go
CREATE PROC sp_Delete_UserAccount
@id_UA int 
AS
	BEGIN
		delete from tblFeedBack where id_UA=@id_UA;
		delete from tblUserDetail where id_UA=@id_UA;
		delete from tblUserAccount where id_UA=@id_UA;
	END
